package com.deng.etl.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.deng.etl.common.IEtlInterface;
import com.deng.etl.common.EtlLog;
//制空
public class ImportOP_count extends ImportBase implements IEtlInterface {
	
	public static String COLUMNS=" 科室ID,科室名称,year(手术日期) as year,month(手术日期) as month,count(手术单号) as count ";
	
	public static String SELECT="select "+COLUMNS+"  from neusoft.JIXIAO_OPERATIONINFO " +
			"where 手术日期>=? and 手术日期<? group by 科室ID,科室名称,year(手术日期),month(手术日期)";
	
	public static String INSERT="insert into t_org_organization_workload_month " +
			"(stat_time,org_operate_organization_id,org_operate_organization_name,create_time,ip_in_count,ip_out_count," +
			"op_count,cure_count,bed_use_rate,bed_use_count,bed_open_count) " +
			"values(?,?,?,?,null,null,null,?,null,null,null)";
	
	public static String UPDATE="update t_org_organization_workload_month set cure_count=(case when cure_count is null then 0 else cure_count end)+? " +
			"where stat_time=? and org_operate_organization_id=?";	
	
	public static String DELETE="update t_org_organization_workload_month set cure_count=null where stat_time>=? and stat_time<?";
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ImportOP_count im=new ImportOP_count();

	}
	
	public boolean checkExist(Connection conn,java.sql.Date date,String id){
		boolean ret=false;
		try {
			System.out.println("---------checkExist-------"+date.toLocaleString()+";"+id);
			PreparedStatement pst =conn.prepareStatement("select stat_time from " +
					"t_org_organization_workload_month where stat_time=? and org_operate_organization_id=?");
			pst.setDate(1, date);
			pst.setString(2, id);
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				ret=true;
			}
			rs.close();
			pst.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	
	
	public void insertData(Connection sourceConn,Connection targetConn,String name,String fromDate,String toDate) {
		System.out.println("从："+fromDate+"   到："+toDate);
		
		Date from=null;
		Date to=null;
		try {
			from = format_s.parse(fromDate);
			to=format_s.parse(toDate);
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		System.out.println("-SELECT-------"+SELECT);
		
		try {
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志

			PreparedStatement pst=sourceConn.prepareStatement(SELECT);
			PreparedStatement pst_insert=targetConn.prepareStatement(INSERT);
			PreparedStatement pst_update=targetConn.prepareStatement(UPDATE);
			pst.setDate(1, getSqlDate(from));
			pst.setDate(2, getSqlDate(to));
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				
				String or_code=rs.getString("科室ID");
				String or_name=rs.getString("科室名称");
				java.sql.Date date=new java.sql.Date(rs.getInt("year")-1900,rs.getInt("month")-1,1);
				boolean exist=checkExist(targetConn,date, or_code);
				//修改
				if(exist){
					pst_update.setInt(1, rs.getInt("count"));
					pst_update.setDate(2, date);
					pst_update.setString(3, or_code);
					update(pst_update);
				}
				else{//新增 
					pst_insert.setDate(1, date);
					pst_insert.setString(2, or_code);
					pst_insert.setString(3, or_name);
					Date date1=new Date();
					pst_insert.setTimestamp(4,getTimestamp(date1));
					pst_insert.setInt(5, rs.getInt("count"));
					
					insert(pst_insert);
				}
			}
			rs.close();
			pst.close();
			pst_insert.close();
			pst_update.close();
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public void deleteData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {
		// TODO Auto-generated method stub
		System.out.println("从："+fromDate+"   到："+toDate);
		
		Date from=null;
		Date to=null;

		try {
			from = format_s.parse(fromDate);
			to = format_s.parse(toDate);
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try{
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			PreparedStatement pst_delete=targetConn.prepareStatement(DELETE);
			java.sql.Date date=new java.sql.Date(from.getYear(),from.getMonth(),1);
			java.sql.Date date1=new java.sql.Date(to.getYear(),to.getMonth(),1);
			pst_delete.setDate(1,date);
			pst_delete.setDate(2,date1);
			pst_delete.executeUpdate();
			pst_delete.close();
				
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
		}
		catch(SQLException e){
			e.printStackTrace();
		}
		
	}
	
	public void updateData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {

		System.out.println("从："+fromDate+"   到："+toDate);
		
		try {
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志

			deleteData(sourceConn, targetConn,name, fromDate, toDate);
			insertData(sourceConn, targetConn,name, fromDate, toDate);
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	}
}
